package com.fy.tt.dao.impl;

import com.fy.toolhelper.db.BaseDaoImpl;
import com.fy.tt.dao.SemanticDao;
import com.fy.tt.entity.Semantic;
import com.fy.tt.entity.query.SemanticQuery;
import com.fy.tt.entity.vo.SemanticVo;
import org.springframework.util.StringUtils;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

public class SemanticDaoImpl extends BaseDaoImpl<Semantic> implements SemanticDao {

    @Override
    public Semantic getSemantic(Connection connection, int wordId) throws SQLException {
        Semantic semantic = null;
        String sql = "select * from bls_semantic where word_id = ?";
        PreparedStatement pstm = connection.prepareStatement(sql);
        pstm.setInt(1, wordId);
        // 返回一条查询结果
        ResultSet rs = pstm.executeQuery();
        while (rs.next()){
            semantic = new Semantic(rs);
        }
        if(rs != null){
            rs.close();
        }
        if (pstm != null){
            pstm.close();
        }
        return semantic;
    }


    @Override
    public int deleteSemantic(Connection connection, int wordId) throws SQLException {
        updateIsTypeInByWordId(connection, wordId, 0);
        String sql = "delete from bls_semantic where word_id = ?";
        PreparedStatement pstm = connection.prepareStatement(sql);
        pstm.setInt(1,wordId);
        int i = pstm.executeUpdate();
        if (pstm != null){
            pstm.close();
        }
        return i;
    }


    @Override
    public int updateIsTypeInByWordId(Connection connection, int wordId, int isTypeIn) throws SQLException {
        String sql = "update bls_word set is_type_in = ? where id = ?";
        PreparedStatement pstm = connection.prepareStatement(sql);
        pstm.setInt(1, isTypeIn);
        pstm.setInt(2, wordId);
        int i = pstm.executeUpdate();
        if (pstm != null){
            pstm.close();
        }
        return i;
    }

    @Override
    public List<SemanticVo> getAllSemantics(Connection connection) throws SQLException {
        String sql = "select s.*,p.band_name from bls_semantic s left join bls_parser p on s.band_obj_id=p.band_obj_id";
        PreparedStatement pstm = connection.prepareStatement(sql);
        ResultSet rs = pstm.executeQuery();
        List<SemanticVo> semantics = new ArrayList<>();
        while(rs.next()){
            SemanticVo semantic = new SemanticVo(rs);
            semantics.add(semantic);
        }
        if(rs != null){
            rs.close();
        }
        if (pstm != null){
            pstm.close();
        }
        return semantics;
    }

    @Override
    public int getTotalSemantic(Connection connection, SemanticQuery semanticQuery) throws SQLException {
        int count = 0;
        String sql = "select s.*,p.band_name from bls_semantic s left join bls_parser p on s.band_obj_id=p.band_obj_id where 1 = 1 ";
        if(!StringUtils.isEmpty(semanticQuery.getName())){
            sql+=" and ( s.name like '%" + semanticQuery.getName() + "%'";
            sql+=" or p.band_name like '%" + semanticQuery.getName() + "%'";
            sql+=" or s.description like '%" + semanticQuery.getName() + "%' )";
        }
        if(!StringUtils.isEmpty(semanticQuery.getIsTypeIn())){
            sql+=" and s.is_type_in = " + semanticQuery.getIsTypeIn();
        }
        if(!StringUtils.isEmpty(semanticQuery.getBandObjId())){
            sql+=" and s.band_obj_id = " + semanticQuery.getBandObjId();
        }
        if(!StringUtils.isEmpty(semanticQuery.getIsRange())){
            sql+=" and s.is_range = " + semanticQuery.getIsRange();
        }

        if(StringUtils.isEmpty(semanticQuery.getName())
                && StringUtils.isEmpty(semanticQuery.getIsTypeIn())
                && StringUtils.isEmpty(semanticQuery.getBandObjId())
                && StringUtils.isEmpty(semanticQuery.getIsRange())){
            sql = "select w.*,p.band_name from bls_semantic w left join bls_parser p on w.band_obj_id=p.band_obj_id ";
        }
        PreparedStatement pstm = connection.prepareStatement(sql);
        ResultSet rs = pstm.executeQuery();
        rs.last();
        count = rs.getRow();
        if(rs != null){
            rs.close();
        }
        if (pstm != null){
            pstm.close();
        }
        return count;
    }

    @Override
    public int getTotalSemanticDeveloped(Connection connection, SemanticQuery semanticQuery) throws SQLException {
        int count = 0;
        String sql = "select w.*,s.*,p.band_name from bls_semantic s LEFT JOIN bls_word w on w.id = s.word_id  left join bls_parser p on s.band_obj_id=p.band_obj_id where w.is_type_in = 1  ";
        if(!StringUtils.isEmpty(semanticQuery.getName())){
            sql+=" and ( s.name like '%" + semanticQuery.getName() + "%'";
            sql+=" or p.band_name like '%" + semanticQuery.getName() + "%'";
            sql+=" or s.description like '%" + semanticQuery.getName() + "%' )";
        }
        if(!StringUtils.isEmpty(semanticQuery.getIsTypeIn())){
            sql+=" and s.is_type_in = " + semanticQuery.getIsTypeIn();
        }
        if(!StringUtils.isEmpty(semanticQuery.getBandObjId())){
            sql+=" and s.band_obj_id = " + semanticQuery.getBandObjId();
        }
        if(!StringUtils.isEmpty(semanticQuery.getIsRange())){
            sql+=" and s.is_range = " + semanticQuery.getIsRange();
        }

        if(StringUtils.isEmpty(semanticQuery.getName())
                && StringUtils.isEmpty(semanticQuery.getIsTypeIn())
                && StringUtils.isEmpty(semanticQuery.getBandObjId())
                && StringUtils.isEmpty(semanticQuery.getIsRange())){
            sql = "select w.*,s.*,p.band_name from bls_semantic s LEFT JOIN bls_word w on w.id = s.word_id  left join bls_parser p on s.band_obj_id=p.band_obj_id where w.is_type_in = 1 ";
        }
        PreparedStatement pstm = connection.prepareStatement(sql);
        ResultSet rs = pstm.executeQuery();
        rs.last();
        count = rs.getRow();
        if(rs != null){
            rs.close();
        }
        if (pstm != null){
            pstm.close();
        }
        return count;
    }

    public SemanticDaoImpl() throws Exception {
    }
}
